package com.qst.itoffer.mapper;

import com.qst.itoffer.entity.JobApplyInfo;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
public interface JobApplyMapper {
    @Select("SELECT a.APPLICANT_EMAIL, j.JOB_NAME, c.COMPANY_NAME, ja.APPLY_DATE, ja.APPLY_STATE " +
            "FROM tb_job j " +
            "JOIN tb_jobapply ja ON j.JOB_ID = ja.JOB_ID " +
            "JOIN tb_applicant a ON ja.APPLICANT_ID = a.APPLICANT_ID " +
            "JOIN tb_company c ON j.COMPANY_ID = c.COMPANY_ID")
    List<JobApplyInfo> getJobApplyList();

    @Delete("DELETE FROM tb_job " +
            "WHERE JOB_ID NOT IN (SELECT DISTINCT JOB_ID FROM tb_jobapply) " +
            "AND JOB_ENDTIME < #{currentDate}")
    void deleteInvalidJobs(Date currentDate);
}
